<?php

	session_start();
	if(!($_SESSION["username"])||($_SESSION["role"]!="TUT")){
		//not logged in or tutor
		header("location:index.php");
	}
	
	if (isset($_GET['file'])){
		$filename=$_GET['file'];
			
		//Get Document Path
		$strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"])));
		$OpenFile = "temp/".$filename;
		
		//Create Exce.Application
		$xlApp = new COM("Excel.Application");
		$xlBook = $xlApp->Workbooks->Open($strPath."/".$OpenFile);

		$xlSheet1 = $xlBook->Worksheets(1);

		// Insert to MySQL Database
		$objConnect = mysql_connect("localhost","root","") or die(mysql_error());
		$objDB = mysql_select_db("Project 5.5");
		
		$sql="SELECT User_ID FROM users";
		$result=mysql_query($sql);
		
		$count=0;
		$currentIDs=null;
		
		while ($row = mysql_fetch_row($result)){
			$currentIDs[$count]=$row[0];			
			$count++;
		}
		
		$sql="SELECT User_ID FROM users WHERE Role='STU'";
		$result=mysql_query($sql);
		
		$count=0;
		$currentStudentIDs=null;
		
		while ($row = mysql_fetch_row($result)){
			$currentStudentIDs[$count]=$row[0];			
			$count++;
		}
		
		$i=2;
		while($xlSheet1->Cells->Item($i,1) != ""){
			$SQL = "";
			$SQL2 = "";
			if (in_array("".$xlSheet1->Cells->Item($i,1),$currentStudentIDs)){		// if its an existing student
				$SQL .= "UPDATE users u studentinfo s SET ";
				$SQL .= "u.First_Name='".$xlSheet1->Cells->Item($i,2)."', ";
				$SQL .= "u.Last_Name='".$xlSheet1->Cells->Item($i,3)."', u.Username='".$xlSheet1->Cells->Item($i,4)."', ";
				$SQL .= "u.Password='".$xlSheet1->Cells->Item($i,5)."', u.Email='".$xlSheet1->Cells->Item($i,6)."', ";
				$SQL .= "s.Class_ID='".$xlSheet1->Cells->Item($i,7)."', s.Group_ID='".$xlSheet1->Cells->Item($i,8)."' ";
				$SQL .= "WHERE u.User_ID='".$xlSheet1->Cells->Item($i,1)."' AND s.User_ID='".$xlSheet1->Cells->Item($i,1)."';";
			} else {		
				if(!in_array("".$xlSheet1->Cells->Item($i,1),$currentIDs)&&$xlSheet1->Cells->Item($i,1)!=""){	// if its not a current student
					$SQL .= "INSERT INTO users ";
					$SQL .= "(User_ID,First_Name,Last_Name,Username,Password,Email,Role) ";
					$SQL .= "VALUES ";
					$SQL .= "('".$xlSheet1->Cells->Item($i,1)."','".$xlSheet1->Cells->Item($i,2)."' ";
					$SQL .= ",'".$xlSheet1->Cells->Item($i,3)."','".$xlSheet1->Cells->Item($i,4)."' ";
					$SQL .= ",'".$xlSheet1->Cells->Item($i,5)."','".$xlSheet1->Cells->Item($i,6)."' ";
					$SQL .= ",'STU')";
					
					$SQL2 .= "INSERT INTO studentinfo ";
					$SQL2 .= "(User_ID,Class_ID,Group_ID) ";
					$SQL2 .= "VALUES ";
					$SQL2 .= "('".$xlSheet1->Cells->Item($i,1)."','".$xlSheet1->Cells->Item($i,7)."','".$xlSheet1->Cells->Item($i,8)."');";
				}
			}
						
			mysql_query($SQL);
			mysql_query($SQL2);
			$i++;
		}

		//Close & Quit
		mysql_close($objConnect);
		$xlApp->Application->Quit();
		$xlApp = null;
		$xlBook = null;
		$xlSheet1 = null;
		
		header("location:managestudents.php?message=delete&file=".$_GET['file']);
	}
?>